package com.esdk.sql.orm;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.util.Date;
import java.util.List;

import com.alibaba.fastjson.JSONArray;
import com.esdk.sql.Column;
import com.esdk.sql.Columns;
import com.esdk.sql.Field;
import com.esdk.sql.ILogic;
import com.esdk.sql.IRowMappper;
import com.esdk.sql.ISQL;
import com.esdk.sql.ISelect;
import com.esdk.sql.LogicFactory;
import com.esdk.sql.Select;
import com.esdk.sql.Table;
import com.esdk.sql.Where;
import com.esdk.utils.Constant;

@SuppressWarnings("unchecked")
abstract public class SelectWrapper<S extends ISelect,K extends IRow> implements ISelect{
	protected Select select;
	protected int useCacheSec=Constant.ResetUseCacheSec;;
	protected boolean useSessionCache=false;

	public Select getSelect(){
		parse();
		return select;
	}

	public SelectWrapper(Table table){
		select=new Select(table,null);
	}

	public SelectWrapper(Connection conn,boolean isTop){
		select=new Select(conn,isTop);
	}

	public SelectWrapper(Table table,Connection conn){
		select=new Select(table,conn);
	}

	public SelectWrapper(String tablename,Connection conn){
		select=new Select(tablename,conn);
	}

	/**增加1个输出字段，指定函数名，字段名根据函数名+下划线+字段名自动生成*/
	public S addColumn(String columnname,String functionname){
		this.select.addColumn(columnname,functionname);
		return (S)this;
	}

	/**增加1个输出字段，指定函数名，字段名根据函数名+下划线+字段名自动生成*/
	public S addColumnWithFunction(String columnname,String functionname){
		this.select.addColumn(columnname,functionname);
		return (S)this;
	}

	/**增加1个输出字段，指定函数名和别名*/
	public S addColumn(String columnname,String functionname,String aliasName){
		this.select.addColumn(columnname,functionname,aliasName);
		return (S)this;
	}

	/**增加1个输出字段*/
	public S addColumn(String columnname){
		this.select.addColumn(columnname);
		return (S)this;
	}

	/**增加1个输出字段，手工指定别名*/
	public S addColumn(ISQL select,String aliasName){
		this.select.addColumn(select,aliasName);
		return (S)this;
	}

	/**增加1个输出字段*/
	public S addColumn(Column column){
		this.select.addColumn(column);
		return (S)this;
	}

	/**查询第1条记录的第1个字段*/
	public int queryForInt() throws SQLException{
		parse();
		return this.select.queryForInt();
	}

	/**查询第1条记录的第1个字段*/
	public double queryForDouble() throws SQLException{
		parse();
		return this.select.queryForDouble();
	}

	/**查询第1条记录的第1个字段*/
	public Object queryForObject() throws SQLException{
		parse();
		return this.select.queryForObject();
	}

	/**增加输出字段并指定别名*/
	public S addColumnWithAlias(String columnname,String alias){
		this.select.addColumnWithAlias(columnname,alias);
		return (S)this;
	}

	/**增加输出*字段*/
	public S addAllColumns(){
		this.select.addAllColumns();
		return (S)this;
	}

	/**设置多个输出的字段，不清除之前的输出字段*/
	public S addColumns(Column...cols){
		select.addColumns(cols);
		return (S)this;
	}

	/**设置多个输出的字段，不清除之前的输出字段*/
	public S addColumns(String...columnnames){
		this.select.addColumns(columnnames);
		return (S)this;
	}

	/**设置多个输出的字段，并清除之前的输出字段*/
	public S setColumns(String...columnnames){
		this.select.setColumns(columnnames);
		return (S)this;
	}

	/**设置多个输出的字段，并清除之前的输出字段*/
	public S setColumns(Column...cols){
		this.select.setColumns(cols);
		return (S)this;
	}

	/**增加1个and的where条件*/
	public S addCondition(ILogic value){
		this.select.addCondition(value);
		return (S)this;
	}

	/**增加多个and的where条件*/
	public S addConditions(ILogic... value){
		this.select.addCondition(LogicFactory.logicTree(value));
		return (S)this;
	}

	/**增加where条件*/
	public S addCondition(String fieldName,String expression,Object value){
		this.select.addCondition(fieldName,expression,value);
		return (S)this;
	}

	/**增加where条件，会引起sql注入风险*/
	public S addCondition(String value){
		this.select.addCondition(value);
		return (S)this;
	}

	/**删除指定的等于类型的where条件*/
	public S removeCondition(String fieldName){
		this.select.removeCondition(fieldName);
		return (S)this;
	}

	/**清除所有where条件*/
	public S clearCondition(){
		this.select.clearCondition();
		return (S)this;
	}

	/**清除on条件*/
	public S clearOn(){
		this.select.clearOn();
		return (S)this;
	}

	public S addEq(String fieldName,Number value){
		this.select.eq(fieldName,value);
		return (S)this;
	}

	public S addEq(String fieldName,Object value){
		this.select.eq(fieldName,value);
		return (S)this;
	}

	/**等于*/
	public S addEq(String fieldName,boolean value){
		this.select.eq(fieldName,value);
		return (S)this;
	}

	/**等于*/
	public S addEq(String fieldName,String value){
		this.select.eq(fieldName,value);
		return (S)this;
	}

	/**等于*/
	public S eq(String fieldName,Object value){
		this.select.eq(fieldName,value);
		return (S)this;
	}

	/**等于*/
	public S eq(String fieldName,Number value){
		return addEq(fieldName,value);
	}

	/**等于*/
	public S eq(String fieldName,String value){
		this.select.eq(fieldName,value);
		return (S)this;
	}

	/**等于*/
	public S eq(String fieldName,Boolean value){
		this.select.eq(fieldName,value);
		return (S)this;
	}

	/**大于*/
	public S gt(String fieldName,Number value){
		this.select.gt(fieldName,value);
		return (S)this;
	}

	/**大于*/
	public S gt(String fieldName,String value){
		this.select.gt(fieldName,value);
		return (S)this;
	}

	/**大于*/
	public S gt(String fieldName,Date value){
		this.select.gt(fieldName,value);
		return (S)this;
	}

	/**大于等于*/
	public S gteq(String fieldName,Number value){
		this.select.gteq(fieldName,value);
		return (S)this;
	}

	/**大于等于*/
	public S gteq(String fieldName,String value){
		this.select.gteq(fieldName,value);
		return (S)this;
	}

	/**大于等于*/
	public S gteq(String fieldName,Date value){
		this.select.gteq(fieldName,value);
		return (S)this;
	}

	/**小于*/
	public S lt(String fieldName,Number value){
		this.select.lt(fieldName,value);
		return (S)this;
	}

	/**小于*/
	public S lt(String fieldName,Date value){
		this.select.lt(fieldName,value);
		return (S)this;
	}

	/**小于*/
	public S lt(String fieldName,String value){
		this.select.lt(fieldName,value);
		return (S)this;
	}

	/**小于等于：<=*/
	public S lteq(String fieldName,Number value){
		this.select.lteq(fieldName,value);
		return (S)this;
	}

	/**小于等于：<=*/
	public S lteq(String fieldName,Date value){
		this.select.lteq(fieldName,value);
		return (S)this;
	}

	/**小于等于：<=*/
	public S lteq(String fieldName,String value){
		this.select.lteq(fieldName,value);
		return (S)this;
	}

	/**多个or的等于条件，注意输入时参数顺序和类型不要弄错，sql效果相当于"where name=abc or code=dog"*/
	public S orEq(Object...args){
		return orExpress(Where.EQ,args);
	}

	/**多个or的不等于条件，注意输入时参数顺序和类型不要弄错，sql效果相当于"where name!=abc or code!=dog"*/
	public S orNotEq(Object...args){
		return orExpress(Where.NOTEQUAL,args);
	}

	/**多个or的like条件，注意输入时参数顺序和类型不要弄错，sql效果相当于"where name like %abc% or code like dog%"*/
	public S orLike(Object...args){
		return orExpress(Where.LIKE,args);
	}

	/**支持多个or条件的条件表达式,注意输入时参数顺序和类型不要弄错*/
	public S orExpress(String expression,Object...args){
		select.orExpress(expression,args);
		return (S)this;
	}

	/**支持多个or条件的条件表达式,自动判断参数是内容或表达式*/
	public S or(Object...args){
		select.or(args);
		return (S)this;
	}

	/**支持多个and条件的条件表达式,自动判断参数是内容或表达式*/
	public S and(Object...args){
		select.and(args);
		return (S)this;
	}

	/**and表达式，直接写sql*/
	public S and(String liberalSql){
		select.and(liberalSql);
		return (S)this;
	}

	/**不等于*/
	public S ne(String fieldName,String value){
		return addNe(fieldName,value);
	}

	/**不等于*/
	public S ne(String fieldName,Number value){
		return addNe(fieldName,value);
	}

	/**不等于*/
	public S ne(String fieldName,Boolean value){
		return addNe(fieldName,value);
	}

	/**不等于*/
	public S addNe(String fieldName,String value){
		this.select.notEq(fieldName,value);
		return (S)this;
	}

	/**不等于*/
	public S addNe(String fieldName,Number value){
		this.select.notEq(fieldName,value);
		return (S)this;
	}

	/**不等于*/
	public S addNe(String fieldName,Boolean value){
		this.select.notEq(fieldName,value);
		return (S)this;
	}

	public S addNotEqualCondition(String fieldName,Object value){
		return (S)addNe(fieldName,value);
	}

	public S addNe(String fieldName,Object value){
		this.select.addNotEqualCondition(fieldName,value);
		return (S)this;
	}

	public S addNotNull(String fieldName){
		this.select.addNotNull(fieldName);
		return (S)this;
	}

	public S addEqualEmplyNumeric(String fieldName){
		this.select.addEqualEmplyNumeric(fieldName);
		return (S)this;
	}

	public S addEqualEmplyValue(String fieldName){
		this.select.addEqualEmplyValue(fieldName);
		return (S)this;
	}


	public void addGroupBy(String fieldname){
		this.select.addGroupBy(fieldname);
	}

	public S setGroupBy(String...fieldNames){
		this.select.setGroupBy(fieldNames);
		return (S)this;
	}

	public S setHaving(String having) {
		this.select.setHaving(having);
		return (S)this;
	}

	public S addIn(String fieldName,Date... values){
		select.addInCondition(fieldName,values);
		return (S)this;
	}

	public S addIn(String fieldName,Time... values){
		select.addInCondition(fieldName,values);
		return (S)this;
	}

	/**自动寻找select的输出字段，先找有没有这个字段，找不到就拿主键*/
	public S addIn(String fieldName,ISelect<?> select){
		if(select==null) return (S)this;
		if(select instanceof AbstractSelect<?, ?> as) {
			if (as.getColumns().isEmpty() || as.isOutputAllFields()) {
				as.setColumns(as.hasColumn(fieldName) ? fieldName : as.getPrimaryKeyFieldName());
			}
		}
		this.select.addInCondition(fieldName,select);
		return (S)this;
	}

	/**自动寻找select的输出字段，先找有没有这个字段，找不到就拿主键*/
	public S exists(String fieldName,ISelect<?> select){
		if(select==null) return (S)this;
		if(select instanceof AbstractSelect<?, ?> as) {
			this.getTable().createAliasName();
			if (as.getColumns().isEmpty() || as.isOutputAllFields()) {
				as.setColumns("1");
			}
			as.eq(as.hasColumn(fieldName) ? fieldName : as.getPrimaryKeyFieldName(),this.createField(fieldName));
		}
		this.select.addExists(select);
		return (S)this;
	}

	/**自动寻找select的输出字段，先找有没有这个字段，找不到就拿主键*/
	public S notExists(String fieldName,ISelect select){
		if(select==null) return (S)this;
		if(select instanceof AbstractSelect<?, ?> as) {
			this.getTable().createAliasName();
			if (as.getColumns().isEmpty() || as.isOutputAllFields()) {
				as.setColumns("1");
			}
			as.eq(as.hasColumn(fieldName) ? fieldName : as.getPrimaryKeyFieldName(),this.createField(fieldName));
		}
		this.select.addNotExists(select);
		return (S)this;
	}


	public S addIn(String fieldName,List<?> values){
		this.select.addInCondition(fieldName,values);
		return (S)this;
	}

	public S addIn(String fieldName,Object... values){
		this.select.addInCondition(fieldName,values);
		return (S)this;
	}

	public S addIn(String fieldName,Number...values){
		this.select.addInCondition(fieldName,values);
		return (S)this;
	}

	public S addIn(String fieldName,String...values){
		this.select.addInCondition(fieldName,values);
		return (S)this;
	}

	public S addInNumeric(String fieldName,String...values){
		this.select.addInNumeric(fieldName,values);
		return (S)this;
	}

	public S addLikeCondition(String fieldName,String value){
		return addLike(fieldName,value);
	}

	public S addLike(String fieldName,String value){
		this.select.addLikeCondition(fieldName,value);
		return (S)this;
	}

	/**多个字段、相同关键字的or条件的模糊查询，例如:where name like abc% or code like abc% */
	public S likeOr(String[] fieldNames,String keyword){
		this.select.addLikeOrConditions(fieldNames,keyword);
		return (S)this;
	}

	/**一个字段、多个关键字的or条件的模糊查询，例如:where name like abc% or name like god% */
	public S likeOr(String columnName,String... keywords){
		Object[] args=new Object[keywords.length*2];
		for(int i=0;i<keywords.length;i++) {
			args[i*2]=columnName;
			args[i*2+1]=keywords[i];
		}
		return orLike(args);
	}

	/**一个字段、多个关键字的and条件的模糊查询，例如:where name like %dog% and name like %cat% */
	public S likeAnd(String columnName,String... keywords){
		for(String keyword:keywords) {
			this.addLikeCondition(columnName,keyword);
		}
		return (S)this;
	}

	public S notLike(String fieldName,String value){
		this.select.addNotLikeCondition(fieldName,value);
		return (S)this;
	}

	public S addNotEqualEmplyNumeric(String fieldName){
		this.select.addNotEqualEmplyNumeric(fieldName);
		return (S)this;
	}

	public S addNotEqualEmplyValue(String fieldName){
		this.select.addNotEqualEmplyValue(fieldName);
		return (S)this;
	}

	public S notIn(String fieldName,ISelect value){
		this.select.addNotInCondition(fieldName,value);
		return (S)this;
	}

	public S notIn(String fieldName,AbstractSelect select){
		if(select.getColumns().isEmpty() || select.isOutputAllFields()) {
			select.setColumns(select.hasColumn(fieldName)?fieldName:select.getPrimaryKeyFieldName());
		}
		this.select.addNotInCondition(fieldName,select);
		return (S)this;
	}

	public S notIn(String fieldName,String... value){
		this.select.addNotInCondition(fieldName,value);
		return (S)this;
	}

	public S notIn(String fieldName,Number...value){
		this.select.addNotInCondition(fieldName,value);
		return (S)this;
	}

	public S addOnCondition(Field leftfield,Field rightfield){
		this.select.addOnCondition(leftfield,rightfield);
		return (S)this;
	}

	public S addOnCondition(Field field,Object value){
		this.select.addOnCondition(field,value);
		return (S)this;
	}

	public S addOnCondition(ILogic condition){
		this.select.addOnCondition(condition);
		return (S)this;
	}

	public S addOrderBy(String fieldName,boolean isDesc){
		this.select.addOrderBy(fieldName,isDesc);
		return (S)this;
	}

	public S addOrderBy(String fieldName,SortDirEnum sortDir){
		this.select.addOrderBy(fieldName,SortDirEnum.DESC==sortDir);
		return (S)this;
	}

	public S addOrderBy(String fieldName){
		this.select.addOrderBy(fieldName);
		return (S)this;
	}

	public S addOrderBy(String... fieldName){
		this.select.addOrderBy(fieldName);
		return (S)this;
	}

	public S setOrderBy(String fieldName,boolean isDesc){
		this.select.setOrderBy(fieldName,isDesc);
		return (S)this;
	}

	public S setOrderBy(String fieldName,SortDirEnum sortDir){
		this.select.setOrderBy(fieldName,sortDir==SortDirEnum.DESC);
		return (S)this;
	}

	public S setOrderBy(String... fieldName){
		this.select.setOrderBy(fieldName);
		return (S)this;
	}

	public S setOrderBy(String fieldName){
		this.select.setOrderBy(fieldName);
		return (S)this;
	}

	public void clear(){
		Table t=getTable();
		this.select.clear();
		this.select.setTable(t);
	}

	public Field createField(String fieldname){
		return this.select.createField(fieldname);
	}

	public Column createColumn(String fieldname,String aliasName){
		return this.select.createColumn(fieldname,aliasName);
	}

	public Column createColumn(String columnName){
		return this.select.createColumn(columnName);
	}

	public Connection getConnection(){
		return this.select.getConnection();
	}

	public Columns getColumns(){
		return this.select.getColumns();
	}

	public String getSQL(){
		parse();
		return select.getSQL();
	}

	public Table getTable(){
		return this.select.getTable();
	}

	public int getTop(){
		return this.select.getTop();
	}

	public boolean isDistinct(){
		return this.select.isDistinct();
	}

	abstract public void parse();

	public int perform() throws SQLException{
		return this.select.perform();
	}

	public void setConnection(Connection connection){
		this.select.setConnection(connection);
	}

	public S setDistinct(boolean isdistinct){
		this.select.setDistinct(isdistinct);
		return (S)this;
	}

	public S setTableName(String tablename,String alias,String jointype){
		this.select.setTableName(tablename,alias,jointype);
		return (S)this;
	}

	public S setTableName(String tablename,String alias){
		this.select.getTable().setTableName(tablename);
		this.select.getTable().setAliasName(alias);
		return (S)this;
	}

	public S setTableName(String tablename){
		this.select.getTable().setTableName(tablename);
		return (S)this;
	}

	public S setTableAliasName(String alias){
		select.getTable().setAliasName(alias);
		return (S)this;
	}

	public S setTop(int number){
		this.select.setTop(number);
		return (S)this;
	}

	public S setOffset(int value) {
		this.select.setOffset(value);
		return (S)this;
	}

	public S setRowsLimit(int value) {
		this.select.setRowsLimit(value);
		return (S)this;
	}

	public S setRowsOffset(int value) {
		this.select.setRowsOffset(value);
		return (S)this;
	}

	public Object[][] toArray2() throws SQLException{
		parse();
		return this.select.toArray2();
	}

	public Object[][] toArray2(boolean isOutoutColumns,String...columns) throws SQLException{
		parse();
		return this.select.toArray2(isOutoutColumns,columns);
	}


	public ResultSet toResultSet() throws SQLException{
		parse();
		return this.select.toResultSet();
	}

	public ABResultSet<IRow> toABResultSet() throws SQLException{
		return new ABResultSet(toResultSet());
	}

	public List toList(IRowMappper mapper) throws SQLException{
		parse();
		return select.toList(mapper);
	}

	public List toList(IRowMappper mapper,int start,int limit) throws SQLException{
		parse();
		return select.toList(mapper,start,limit);
	}

	public List toList(Class cls) throws SQLException{
		parse();
		return select.toList(cls);
	}

	public List toList(Class cls,int start,int limit) throws SQLException{
		parse();
		return select.toList(cls,start,limit);
	}

	public JSONArray toJsonArray() throws SQLException{
		parse();
		return select.toABResultSet().toJsonArray();
	}

	public JSONArray toJsonArray(boolean isFormatJavaBeanName) throws SQLException{
		parse();
		return select.toABResultSet().toJsonArray(isFormatJavaBeanName);
	}

	public String toString(){
		return getSQL();
	}

	public String getTableAliasName(){
		return select.getTableAliasName();
	}

	public int count() throws SQLException{
		parse();
		return select.count();
	}

	public S addBetween(String fieldName,Number minvalue,Number maxvalue){
		select.addBetween(fieldName,minvalue,maxvalue);
		return (S)this;
	}

	public S addBetween(String fieldName,Date minvalue,Date maxvalue){
		select.addBetween(fieldName,minvalue,maxvalue);
		return (S)this;
	}

	public S addNotBetween(String fieldName,Number minvalue,Number maxvalue){
		select.addNotBetween(fieldName,minvalue,maxvalue);
		return (S)this;
	}

	public S addNotBetween(String fieldName,Date minvalue,Date maxvalue){
		select.addNotBetween(fieldName,minvalue,maxvalue);
		return (S)this;
	}

	public S addSmartBetween(String fieldName,Date minvalue,Date maxvalue){
		select.addSmartBetween(fieldName,minvalue,maxvalue);
		return (S)this;
	}

	public S addNotSmartBetween(String fieldName,Date minvalue,Date maxvalue){
		select.addNotSmartBetween(fieldName,minvalue,maxvalue);
		return (S)this;
	}

	public S addSmartBetweenOr(String[] fieldNames,Date minvalue,Date maxvalue){
		select.addSmartBetweenOr(fieldNames,minvalue,maxvalue);
		return (S)this;
	}

	public S addSmartBetweenOr(List<String> fieldNames,Date minvalue,Date maxvalue){
		select.addSmartBetweenOr(fieldNames.toArray(new String[0]),minvalue,maxvalue);
		return (S)this;
	}

	public S setJoinType(String join){
		select.setJoinType(join);
		return (S)this;
	}

	public S addEqualNull(String fieldName){
		this.select.addEqualNull(fieldName);
		return (S)this;
	}

	public S showSql(boolean isShowSql){
		this.select.showSql(isShowSql);
		return (S)this;
	}

	@Override public boolean showSql(){
		return this.select.showSql();
	}

	public S useSessionCache(boolean useSessionCache) {
		this.useSessionCache=useSessionCache;
		this.select.useSessionCache(useSessionCache);
		return (S)this;
	}

	public S useCache(int sec) {
		this.select.useCache(sec);
		this.useCacheSec=sec;
		return (S)this;
	}

	public S useCache(boolean ifUseCache) {
		if(ifUseCache && this.useCacheSec==Constant.ResetUseCacheSec){
			this.useCacheSec=Constant.DefaultCacheSec;
		}
		select.useCache(this.useCacheSec);
		return (S)this;
	}


	public S retry(int retryTimes){
		this.select.retry(retryTimes);
		return (S)this;
	}

	public S retry(int retryTimes,int retryIntervalMs){
		this.select.retry(retryTimes,retryIntervalMs);
		return (S)this;
	}

}
